﻿using Chaotic.Core.Extension;
using Chaotic.DataBase.DBAccessLibrary.DBAccessLibrary;
using Chaotic.DataBase.DBAccessLibrary.Interface;
using Chaotic.DataBase.DBAccessLibrary.Model;
using Chaotic.Module.DBTool.Common;
using Chaotic.Module.DBTool.Models;
using Chaotic.Module.DBTool.Views;
using Microsoft.Win32;
using System.Data;
using System.Diagnostics;

namespace Chaotic.Module.DBTool.ViewModels
{
    public class OutPutGridContentViewModel: BindableBase
    {
        private readonly IEventAggregator _eventAggregator;
        const int RowMaxCount = 10000;
        string _sql;
        DataSet _ds;

        public OutPutGridContentViewModel(IEventAggregator eventAggregator)
        {
            _eventAggregator = eventAggregator;
            

            ExceptToExcelCommand = new DelegateCommand(() =>
            {
                try
                {
                    if (GridDataView != null && GridDataView.DataViewManager?.DataSet != null)
                        DataSetToExcel(GridDataView.DataViewManager.DataSet);
                }
                catch (Exception ex)
                {
                    WriteLog(ex.Message, true);
                }
            },
            ()=> GridDataView != null);

            ExceptToSqlCommand = new DelegateCommand(() =>
            {
                string dataSetToInsertSql =  DBOperatorFactory.GetGrammar(SysCache.DBConnection).GetDataSetToInsertSql(_sql, _ds, 
                    (string msg) => MessageBoxExtension.ShowConfirm(msg, "提示"), out _);
                if (!string.IsNullOrEmpty(dataSetToInsertSql))
                {
                    //输出到一个新的sql编辑器窗口
                    eventAggregator.SendMessage<DBToolMessage>(new DBToolMessage
                    {
                        Target = nameof(DBMasterContent),
                        ActionName = "AddSqlDocument",
                        Data = new SqlDocumentData()
                        { 
                            IsNewFile = true,
                            SqlContent = new SqlContentMessage()
                            {
                                Sql = dataSetToInsertSql,
                                Status = SqlControlStatus.NewLine
                            }
                        }
                    });
                }
            }, () => GridDataView != null);

            SubmitChangeCommand = new DelegateCommand(() =>
            {
                try
                {
                    List<TableReferenceInfo> tableReferences = SqlParserManager.Instance.ParseTablesWithAliases(_sql,out _);
                    TableReferenceInfo table = tableReferences.First();

                    TableEntity? tableEntity = SysCache.TableList.FirstOrDefault(o => o.Name?.ToLower() == table.TableName.ToLower());
                    if (tableEntity == null)
                        return;
                    List<FieldEntity> fieldEntities = tableEntity.FieldNodes.Where(o => o.IsPrimaryKey).ToList();
                    if (fieldEntities.Count == 0)
                        return;

                    List<ExecSqlModel> execSqlModels = DBOperatorFactory.GetGrammar(SysCache.DBConnection).SubmitDataChange(_ds, table.TableName);
                    if (execSqlModels.Count <= 0)
                    {
                        return;
                    }

                    if (MessageBoxExtension.ShowConfirm("确定提交更改过的内容?", "提示"))
                    {
                        foreach (ExecSqlModel key in execSqlModels)
                        {  
                            DBOperatorFactory.GetOperator.ExecNonQuery(key.Sql, SysCache.DBConnection, key.Params);
                        }
                        _ds.AcceptChanges();
                        WriteLog("数据修改成功！");
                    }
                    else
                    {
                        _ds.RejectChanges();
                        GridDataView = _ds.Tables[0].DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    WriteLog(ex.Message, true);
                    _ds.RejectChanges();
                    GridDataView = _ds.Tables[0].DefaultView;
                }
            }, () =>
            {
                if (_ds == null)
                    return false;
                //判断sql 中如果只有一张表， 代表可以被更新
                List<TableReferenceInfo> tableReferences = SqlParserManager.Instance.ParseTablesWithAliases(_sql,out _);
                if(tableReferences.Count == 0)
                    return false;
                TableReferenceInfo table = tableReferences.First();
                //还需要判断列中是否包含主键
                TableEntity? tableEntity = SysCache.TableList.FirstOrDefault(o => o.Name?.ToLower() == table.TableName.ToLower());
                if (tableEntity == null)
                    return false;
                List<FieldEntity> fieldEntities = tableEntity.FieldNodes.Where(o => o.IsPrimaryKey).ToList();
                if (fieldEntities.Count == 0)
                    return false;
                foreach(var field in fieldEntities)
                {
                    //判断列中是否包含主键
                    if (!_ds.Tables[0].Columns.Contains(field.Name))
                        return false;
                }
                return true;
            });
        }

        public Action FocusTabActive { get; set; }

        private DataView _dataView;
        public DataView? GridDataView {
            get => _dataView;
            set
            {
                if (value != _dataView)
                {
                    _dataView = value;
                    this.RaisePropertyChanged("GridDataView");
                }
            }
        }

        BusyInfoModel _busyModel = new BusyInfoModel();
        public BusyInfoModel BusyModel
        {
            get => _busyModel;
            set
            {
                if (!Equals(_busyModel, value))
                {
                    _busyModel = value;
                    RaisePropertyChanged("BusyModel");
                }
            }
        }

        private string _execTime = "";
        public string ExecTime
        {
            get => _execTime;
            set
            {
                if (value != _execTime)
                {
                    _execTime = value;
                    this.RaisePropertyChanged("ExecTime");
                }
            }
        }

        private string _dataCount= "";
        public string DataCount
        {
            get => _dataCount;
            set
            {
                if (value != _dataCount)
                {
                    _dataCount = value;
                    this.RaisePropertyChanged("DataCount");
                }
            }
        }

        public DelegateCommand ExceptToExcelCommand { get; set; }

        public DelegateCommand ExceptToSqlCommand { get; set; }

        public DelegateCommand SubmitChangeCommand { get; set; }

        public async void LoadDataAsync(string sql)
        {
            await Task.Factory.StartNew(() =>
            {
                _sql = sql.Replace(Environment.NewLine, " ").Replace("\n", " ").Replace("\r", " ");

                //先清空数据
                GridDataView = null;
                BusyModel.IsBusy = true;
                bool isError = false;
                Stopwatch sw = Stopwatch.StartNew();
                try
                {
                    //去除掉前缀符号
                    sql = sql.Trim().TrimStart('\n', '\r', '\t');
                    IGrammar? grammar = DBOperatorFactory.GetGrammar(SysCache.DBConnection);
                    if (grammar == null)
                        return;

                    //1: 验证是否是存储过程
                    if (grammar.GetExecProcInfo(sql, out var procName, out var paramlist))
                    {
                        object obj = DBOperatorFactory.GetOperator.ExecProc(procName, paramlist, out var outdic);
                        if (outdic is { Count: > 0 })
                        {
                            foreach (string key in outdic.Keys)
                            {
                                WriteLog($"{key}-->{outdic[key]}");
                            }
                        }

                        if (obj is DataSet dataset)
                        {
                            _ds = dataset;
                        }
                        else
                        {
                            WriteLog("Result->" + obj);
                        }
                        return;
                    }

                    try
                    {
                        //2: 验证sql语句的总个数，如果数量太大 则进行提醒
                        string sqlcount = $"select count(*) from ({sql}) t";
                        object obj2 = DBOperatorFactory.GetOperator.ExecScalar(sqlcount);
                        if (obj2 != null)
                        {
                            decimal count = Convert.ToDecimal(obj2.ToString());
                            if (count > RowMaxCount)
                            {
                                if (!MessageBoxExtension.ShowConfirm($"当前查询结果为{count}，可能会影响查询速度，是否继续进行?", "提醒"))
                                {
                                    return;
                                }
                            }
                        }
                    }
                    catch { }

                    //3: 执行查询
                    if (sql.StartsWith("update", StringComparison.CurrentCultureIgnoreCase) ||
                        sql.StartsWith("insert", StringComparison.CurrentCultureIgnoreCase) ||
                        sql.StartsWith("delete", StringComparison.CurrentCultureIgnoreCase))
                    {
                        DataCount = DBOperatorFactory.GetOperator.ExecNonQuery(sql).ToString();
                    }
                    else
                    {
                        DataSet ds = DBOperatorFactory.GetOperator.ExecSqlToDS(sql);
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            ApplicationExtension.InvokeOnUIThread(() =>
                            {
                                FocusTabActive?.Invoke();
                                _ds = ds;
                            });
                        }
                    }
                }
                catch (Exception ex)
                {
                    WriteLog(ex.Message, true);
                    isError = true;
                }
                finally
                {
                    BusyModel.IsBusy = false;

                    sw.Stop();
                    ExecTime = sw.Elapsed.ToString("c");

                    if (_ds != null && _ds.Tables.Count > 0)
                    {
                        ApplicationExtension.InvokeOnUIThread(() =>
                        {
                            //新增列 RowNumber
                            if (!_ds.Tables[0].Columns.Contains("ROWNUMBER"))
                            {
                                _ds.Tables[0].Columns.Add("ROWNUMBER", typeof(int));
                                for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
                                    _ds.Tables[0].Rows[i]["ROWNUMBER"] = i;
                            }

                            _ds.AcceptChanges();
                            GridDataView = _ds.Tables[0].DefaultView;
                            DataCount = $"{_ds.Tables[0].Rows.Count} 行";
                        });
                    }


                    ExceptToExcelCommand.RaiseCanExecuteChanged();
                    ExceptToSqlCommand.RaiseCanExecuteChanged();
                    SubmitChangeCommand.RaiseCanExecuteChanged();

                    if (!isError)
                    {
                        WriteLog(string.Format("执行成功.-----耗时:{0}-----行数:{1}",
                            sw.Elapsed, DataCount));
                    }
                }
            });
        }

        public async void ExecSqlAsync(List<string> sqls)
        {
            if (sqls == null || sqls.Count == 0)
                return;

            await Task.Factory.StartNew(() =>
            { 
                //先清空数据
                GridDataView = null;
                BusyModel.IsBusy = true;
                bool isError = false;
                Stopwatch sw = Stopwatch.StartNew();
                try
                {
                    //去除掉前缀符号 
                    IGrammar? grammar = DBOperatorFactory.GetGrammar(SysCache.DBConnection);
                    if (grammar == null)
                        return;

                    int successCount = 0;
                    foreach (string sql in sqls)
                    {
                        //1: 验证是否是存储过程
                        if (grammar.GetExecProcInfo(sql, out var procName, out var paramlist))
                        {
                            object obj = DBOperatorFactory.GetOperator.ExecProc(procName, paramlist, out var outdic);
                            if (outdic is { Count: > 0 })
                            {
                                foreach (string key in outdic.Keys)
                                {
                                    WriteLog($"{key}-->{outdic[key]}");
                                }
                            }

                            if (obj is DataSet dataset)
                            {
                                _ds = dataset;
                            }
                            else
                            {
                                WriteLog("Result->" + obj);
                            }
                            successCount++;
                        }
                        else
                        {
                            //3: 执行查询
                            if (sql.StartsWith("update", StringComparison.CurrentCultureIgnoreCase) ||
                                sql.StartsWith("insert", StringComparison.CurrentCultureIgnoreCase) ||
                                sql.StartsWith("delete", StringComparison.CurrentCultureIgnoreCase))
                            {
                                object? obj = DBOperatorFactory.GetOperator.ExecScalar(sql);
                                successCount++;
                            }
                        }
                    }

                    DataCount = successCount.ToString();
                }
                catch (Exception ex)
                {
                    WriteLog(ex.Message, true);
                    isError = true;
                }
                finally
                {
                    BusyModel.IsBusy = false;

                    sw.Stop();
                    ExecTime = sw.Elapsed.ToString("c");

                    if (_ds != null && _ds.Tables.Count > 0)
                    {
                        ApplicationExtension.InvokeOnUIThread(() =>
                        {
                            //新增列 RowNumber
                            if (!_ds.Tables[0].Columns.Contains("ROWNUMBER"))
                            {
                                _ds.Tables[0].Columns.Add("ROWNUMBER", typeof(int));
                                for (int i = 0; i < _ds.Tables[0].Rows.Count; i++)
                                    _ds.Tables[0].Rows[i]["ROWNUMBER"] = i;
                            }

                            _ds.AcceptChanges();
                            GridDataView = _ds.Tables[0].DefaultView;
                            DataCount = $"{_ds.Tables[0].Rows.Count} 行";
                        });
                    }


                    ExceptToExcelCommand.RaiseCanExecuteChanged();
                    ExceptToSqlCommand.RaiseCanExecuteChanged();
                    SubmitChangeCommand.RaiseCanExecuteChanged();

                    if (!isError)
                    {
                        WriteLog(string.Format("执行成功.-----耗时:{0}-----行数:{1}",
                            sw.Elapsed, DataCount));
                    }
                }
            });
        }

        private void WriteLog(string msg, bool isError = false)
        {
            _eventAggregator.SendLogMessage(msg, isError);
        }

        private void DataSetToExcel(DataSet ds)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.CheckFileExists = false;
            saveFileDialog.CheckPathExists = false;
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "保存为Excel文件"; 
            bool? flag = saveFileDialog.ShowDialog();
            if (flag.HasValue && flag.Value)
            {
                ExcelExtension.ExportToExcel(ds, saveFileDialog.FileName, new string[1] { "ROWNUMBER" });
            }
        }
         
    }
}
